import pymysql
from datetime import date
from flask import Flask,request,jsonify,Response,render_template
from apscheduler.schedulers.background import BackgroundScheduler
from KeyMouseApi import main,get_count,clear_count
from pyecharts.charts import Bar, Line, Grid
from pyecharts import options as opts
from markupsafe import Markup
from jinja2 import Environment, FileSystemLoader
from pyecharts.globals import CurrentConfig

# 关于 CurrentConfig，可参考 [基本使用-全局变量]
# CurrentConfig.GLOBAL_ENV = Environment(loader=FileSystemLoader("./templates"))
app = Flask(__name__)
# 初始化SQLAlchemy，用于和SQLite数据库交互

def tuple_to_dict(sql,tuples)->list[dict]:
    # print("转化函数传入参数",sql,tuples)
    if "select" in sql and tuples not in [(),[],{},"",None]:
        return_result = []
        a = list(map(lambda x: x.lower(), sql.split(" ")))
        # print("a的值",a)
        b = a[a.index("select")+1:a.index("from")]
        # print("b的值",b)
        c = "".join(b).split(",")
        # print("c的值",c)
        if len(c):
            keys = list(map(lambda x: x.strip('`'), c))
            # print("keys的值",keys)
            for i in tuples:
                r = {}
                for idx,key in enumerate(keys):
                    if key == "date":
                        r[key] = i[idx].strftime("%Y-%m-%d")
                    else:
                        r[key] = i[idx]
                return_result.append(r)
                # print("返回结果", r)
        # print("元组转化为字典列表：", return_result)
        return return_result
    else:
        return []
def connect_to_database(sqls):
    # 建立数据库连接
    connection = pymysql.connect(
        host='localhost',  # 数据库主机地址
        user='root',       # 用户名
        password='root',  # 用户密码
        database='keymousedb',  # 数据库名
        charset='utf8mb4'
    )
    sqls = list(filter(lambda x: len(x), sqls))
    result = []
    try:
        with connection.cursor() as cursor:
            # 执行SQL查询
            # sql = "SELECT VERSION()"
            for sql in sqls:
                cursor.execute(sql)
                r = cursor.fetchall()
                print("执行sql结果为:", r)
                result.append(tuple_to_dict(sql, r))
                # print("Database excute result:", result)
        # 提交事务
        connection.commit()
    except pymysql.Error as e:
        print("Error:", e)
    finally:
        # 关闭连接
        connection.close()
        result = list(filter(lambda x: len(x), result))
        # print("连接数据库后返回的值", result)
        if len(result):
            return result
        else:
            return []

def write_to_database():
    today = date.today()
    keydict,mousedict = get_count()
    print("keydict,mousedict", keydict,mousedict)
    
    ### 改造这里 直接用sql进行累加
    mouse_sql = ""
    if mousedict["left"] or mousedict["middle"] or mousedict["right"]:
        mouse_sql = f"""
            INSERT INTO mousecount (`date`, `left`, `middle`, `right`)
            VALUES ({"'%s', ceil(%d/2), %d, ceil(%d/2)" % (str(today), mousedict["left"], mousedict["middle"], mousedict["right"])})
            ON DUPLICATE KEY UPDATE
                `left` = ceil({mousedict["left"]/2}) + `left`,
                `middle` = {mousedict["middle"]} + `middle`,
                `right` = ceil({mousedict["right"]/2}) + `right`
        """
    # 处理键盘点击数据写入
    # ### 这里也构造条件插入
    values = []
    for key_code, count in keydict.items():
        # 将累加的键盘点击次数累加到数据库记录中
        values.append(f"('{str(today)}', '{key_code}', {count})")
    key_sql = ""
    if len(values): 
        key_sql = f"""
            INSERT INTO keycount (`date`, `key`, `count`)
            VALUES {",".join(values)}
            ON DUPLICATE KEY UPDATE
                `count` = case  
                    {" ".join([" when `date` = '%s' and `key` = '%s' then %d + `count` " % (str(today),str(key_code),count) for key_code,count in keydict.items()])}
                    else `count` 
                end
        """
    print("键鼠sql", key_sql)
    print("鼠标sql", mouse_sql)
    # 重置键盘点击累加器
    # 重置鼠标点击累加器
    clear_count()
    # 写入数据库
    connect_to_database([mouse_sql, key_sql])

def get_data(date):
    get_key_sql = """
        select `date`,`key`,`count` from keycount where `date` = '%s'
        """ % (date)
    get_mouse_sql = """
        select `date`,`right`,`left`,`middle` from mousecount where `date` = '%s'
        """ % (date)
    
    return connect_to_database([get_key_sql,get_mouse_sql])

def get_html():
    data = get_data(request.args.get("date",str(date.today())))
    # data = get_data("2025-01-05")
    if len(data):
        key,mouse = data
    else :
        key,mouse = [],[]
    # print("key的类型是:", type(key))
    # print("key的内容是:", key)
    # print("mouse的类型是:", type(mouse))
    # print("mouse的内容是:", mouse)
    kx = [i["key"] for i in key ]
    ky = [i["count"] for i in key]
    bar = (
        Bar()
        .add_xaxis(kx)
        .add_yaxis("点击次数", ky)
        .set_global_opts(title_opts=opts.TitleOpts(title="键盘点击次数分布"))
    )
    my = [i[k] for i in mouse for k in i.keys() if k != "date"] 
    mx = [k for i in mouse for k in i.keys() if k != "date"] 
    # 创建第二个图表：折线图
    line = (
        Line()
        .add_xaxis(mx)
        .add_yaxis("鼠标点击次数",my)
        .set_global_opts(title_opts=opts.TitleOpts(title="鼠标点击次数"))
    )
    result = {"key":bar.dump_options_with_quotes(),"mouse":line.dump_options_with_quotes()}
    # print("html_content", html_content)
    return result
# 首页一个html
@app.route('/')
def index():
    return render_template("1.html")

# 这里用一个接口来返回图表
@app.route('/charts')
def charts():
    result = get_html()
    # print(result)
    return jsonify({
        "code": 200,
        "data": result
    })
    # return result["key"]
def run_app():
    # 同等替换 可以写一个while循环睡眠 执行函数，然后呢再新建一个线程执行就行
    scheduler = BackgroundScheduler()
    # 添加定时任务，每1分钟执行一次write_to_database函数
    scheduler.add_job(write_to_database, 'interval', minutes=0.2)
    scheduler.start()
    # 运行keymouse监听 键鼠
    main()
    # 运行函数
    app.run(debug=True)

if __name__ == '__main__':
    run_app()
    # 测试
    # charts()